package com.su.middle.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.su.middle.db.connection.ConnectionHelper;
import com.su.middle.db.entity.Proposals;

public class ProposalsDAO {
	
	private static final String INSERT_PROPOSAL = "insert into proposals (student_name, supervisor_name, thesis_title, status) " +
												  " values (?, ?, ?, ?)";
	private static final String STUDENT_PROPOSALS = "select * from proposals where lower(student_name) = lower(?)";
	
	private static final String UPDATE_PROPOSAL = "update proposals set status = ? where id = ?";
	
	public int createProposal(Proposals proposal) {
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rs = null;
		int key = -1;
		
		try {
		
			if (proposal != null) {
				conn = ConnectionHelper.getConnection();
				stm = conn.prepareStatement(INSERT_PROPOSAL, Statement.RETURN_GENERATED_KEYS);
				stm.setString(1, proposal.getStudentName());
				stm.setString(2, proposal.getSupervisorName());
				stm.setString(3, proposal.getThesisTitle());
				stm.setString(4, proposal.getStatus());
				stm.executeUpdate();

			    rs = stm.getGeneratedKeys();

			    if (rs.next()) {
			        key = rs.getInt(1);
			    } 
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {			
			closeConnection(conn);
			closeStatement(stm);
			closeResultSet(rs);
		}
		
		return key;
	}
	
	public ArrayList<Proposals> getStudentProposals(String studentName) {
		ArrayList<Proposals> result = new ArrayList<Proposals>();
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try {
			
			if (studentName != null) {
				conn = ConnectionHelper.getConnection();
				stm = conn.prepareStatement(STUDENT_PROPOSALS);
				stm.setString(1, studentName);
				rs = stm.executeQuery();
				while (rs.next()) {
					try {
						int id = rs.getInt("id");
						String sName = rs.getString("student_name");
						String supName = rs.getString("supervisor_name");
						String title = rs.getString("thesis_title");
						String status = rs.getString("status");
						Proposals p = new Proposals(id, sName, supName, title, status);
						result.add(p);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
			
		} catch (Exception e) {
			
		} finally {			
			closeConnection(conn);
			closeStatement(stm);
			closeResultSet(rs);
		}
		
		return result;
	}
	
	public void updateProposal(int proposalId, String newStatus) {
		Connection conn = null;
		PreparedStatement stm = null;
		
		try {
		
			if (proposalId > 0 && newStatus != null) {
				conn = ConnectionHelper.getConnection();
				stm = conn.prepareStatement(UPDATE_PROPOSAL);
				stm.setString(1, newStatus);
				stm.setInt(2, proposalId);
				stm.executeUpdate();
			}
			
		} catch (Exception e) {
			
		} finally {			
			closeConnection(conn);
			closeStatement(stm);
		}
	}
	
	private void closeConnection(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {				
			e.printStackTrace();
		}
	}
	
	private void closeStatement(PreparedStatement stm) {
		try {
			if (stm != null) {
				stm.close();
			}
		} catch (SQLException e) {					
			e.printStackTrace();
		}
	}
	
	private void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {					
			e.printStackTrace();
		}
	}	
	
	public static void main (String[] sa) {
		Proposals p = new Proposals(12, "Ivo", "Bla", "stuff", "P");
		ProposalsDAO dao = new ProposalsDAO();
		int a = dao.createProposal(p);
		System.out.println(a);
		
	}
	
}
